﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entity;
using System.Data.SqlClient;
using System.Data;
namespace DAL
{
    public class ProductTypeDB : DBAccess
    {
        private const string ID = "TypeId";
        private const string NAME = "Name";
        private const string DESCRIPTION = "Description";
        private const string VISIBLE = "Visible";

        /// <summary>
        /// return all productType
        /// </summary>
        /// <param name="parent"></param>
        /// <returns></returns>
        public IList<ProductType> GetAll(ProductType parent)
        {
            IList<ProductType> listProduct = new List<ProductType>();
            SqlDataReader rd = null;

            this.Connect();

            if (parent == null) // return all productType is parent
            {
                rd = this.Returncommand("SELECT * FROM ProductType WHERE parentid is null");
            }
            else
            {
                //return all product type is child of  parent
                rd = this.Returncommand("SELECT * FROM ProductType WHERE parentid = " + parent.TypeId.ToString());
            }
            while (rd.Read())
            {
                ProductType _producttype = new ProductType();
                _producttype.TypeId = int.Parse(rd[ID].ToString());
                _producttype.Name = rd[NAME].ToString();
                _producttype.ParentType = parent;
                _producttype.Description = rd[DESCRIPTION].ToString();
                _producttype.Visible = bool.Parse(rd[VISIBLE].ToString());

                listProduct.Add(_producttype);
            }

            this.Disconnect();

            return listProduct;
        }

        public ProductType GetItemByID(int Id, IList<ProductType> _listProduct)
        {
            foreach (ProductType item in _listProduct)
            {
                if (item.TypeId == Id)
                {
                    return item;
                }
            }
            return null;
        }

        /// <summary>
        /// Insert a productType
        /// </summary>
        /// <param name="fproducttype"></param>
        public void InsertProductType(ProductType productType)
        {
            int visible = (productType.Visible == false) ? 0 : 1;
            string query = string.Format("INSERT INTO ProductType(Name,Description,ParentId,Visible) VALUES('{0}','{1}',{2},{3})",
               productType.Name, productType.Description, productType.ParentType.TypeId, visible);

            ExecuteNonQuery(query, CommandType.Text);
        }

        public void InsertType(ProductType productType)
        {
            string query = string.Format("INSERT INTO ProductType(Name,Description, Visible) VALUES('{0}','{1}','{2}')",
               productType.Name, productType.Description,0);

            ExecuteNonQuery(query, CommandType.Text);
        }

        /// <summary>
        /// modify ProductType
        /// </summary>
        /// <param name="productType"></param>
        public void UpdateProductType(ProductType productType)
        {
            int visible = (productType.Visible == false) ? 0 : 1;
            string query = string.Format("update productType set name='{0}', description='{1}', parentId={2},visible={3} WHERE TypeId={4}",
                productType.Name, productType.Description, productType.ParentType.TypeId, visible, productType.TypeId);
            ExecuteNonQuery(query, CommandType.Text);
            IList<ProductType> _listProduct = new List<ProductType>();
            _listProduct = GetAll(productType);
            foreach (ProductType item in _listProduct)
            {
                item.Visible = productType.Visible;
                UpdateProductType(item);

            }
        }

        /// <summary>
        /// delete a product Type
        /// </summary>
        /// <param name="productType"></param>
        public void DeleteProductType(ProductType productType)
        {
            string query = string.Format("DELETE FROM productType WHERE TypeId={0}", productType.TypeId);

            ExecuteNonQuery(query, CommandType.Text);
        }

        /// <summary>
        /// check product have chill ?
        /// </summary>
        /// <param name="productType"></param>
        /// <returns>bool</returns>
        public bool CheckHaveChild(ProductType productType)
        {
            string query = "SELECT COUNT(TypeId) FROM ProductType WHERE parentId=" + productType.TypeId.ToString();

            return Check(query);
        }

        /// <summary>
        /// check have products depend productType
        /// </summary>
        /// <param name="productType"></param>
        /// <returns>bool</returns>
        public bool checkProduct(ProductType productType)
        {
            string query = "SELECT COUNT(productId) FROM Products WHERE Typeid=" + productType.TypeId.ToString();

            return Check(query);
        }

        public IList<ProductType> GetProductTypeNoHaveChild()
        {
            IList<ProductType> listProduct = new List<ProductType>();

            this.Connect();

            string sql = "SELECT a.* FROM producttype as a WHERE typeid not in(SELECT b.parentid FROM producttype as b WHERE b.parentid=a.typeid)";
            SqlDataReader rd = ExecuteQuery(sql, CommandType.Text);
            while (rd.Read())
            {
                ProductType producttype = new ProductType();
                producttype.TypeId = int.Parse(rd[ID].ToString());
                producttype.Name = rd[NAME].ToString();
                producttype.Description = rd[DESCRIPTION].ToString();
                producttype.Visible = bool.Parse(rd[VISIBLE].ToString());
                listProduct.Add(producttype);
            }

            this.Disconnect();

            return listProduct;
        }

        /// <summary>
        /// count product Type
        /// </summary>
        /// <param name="productType"></param>
        /// <returns></returns>
        public int CountProductType(ProductType productType)
        {
            this.Connect();
            string query = string.Format("SELECT COUNT(*) FROM ProductType WHERE Name ='{0}' AND ParentId='{1}'", productType.Name, productType.ParentType.TypeId);
            SqlCommand cmd = new SqlCommand(query, conn);
            int n = int.Parse((cmd.ExecuteScalar().ToString()));
            this.Disconnect();
            return n;
        }

    }
}
